﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace DataAccessLayer
{
    class clsDataAccessHelper
    {
        protected static string _ConnectionString = ConfigurationSettings.AppSettings["ConnectionString"];

        public static string ConnectionString
        {
            get
            {
                return _ConnectionString;
            }
        }

        public static int ExecNonQuery(string _sp, List<SqlParameter> _list)
        {
            int n;
            try
            {
                SqlConnection cnn = new SqlConnection(ConnectionString);
                cnn.Open();
                try
                {
                    SqlCommand cmd = cnn.CreateCommand();
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = _sp;
                    if (_list != null)
                    {
                        foreach (SqlParameter para in _list)
                        {
                            cmd.Parameters.Add(para);
                        }
                    }
                    n = cmd.ExecuteNonQuery();
                }
                catch (SqlException ex)
                {
                    throw ex;
                }
                finally
                {
                    cnn.Close();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return n;
        }

        public static DataTable ExecuteQueryNonStoredProcedure(String _sp, List<SqlParameter> _list)
        {
            DataTable dt = new DataTable();
            try
            {
                SqlConnection cnn = new SqlConnection(ConnectionString);
                cnn.Open();
                try
                {
                    SqlCommand cmd = cnn.CreateCommand();
                    cmd.CommandText = _sp;
                    if (_list != null)
                    {
                        foreach (SqlParameter para in _list)
                        {
                            cmd.Parameters.Add(para);
                        }
                    }
                    SqlDataAdapter adapter = new SqlDataAdapter();
                    adapter.SelectCommand = cmd;
                    adapter.Fill(dt);
                }
                catch (SqlException ex)
                {
                    throw ex;
                }
                finally
                {
                    cnn.Close();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }

        public static DataTable ExecuteQuery(String _sp, List<SqlParameter> _list)
        {
            DataTable dt = new DataTable();
            try
            {
                SqlConnection cnn = new SqlConnection(ConnectionString);
                cnn.Open();
                try
                {
                    SqlCommand cmd = cnn.CreateCommand();
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = _sp;
                    if (_list != null)
                    {
                        foreach (SqlParameter para in _list)
                        {
                            cmd.Parameters.Add(para);
                        }
                    }
                    SqlDataAdapter adapter = new SqlDataAdapter();
                    adapter.SelectCommand = cmd;
                    adapter.Fill(dt);
                }
                catch (SqlException ex)
                {
                    throw ex;
                }
                finally
                {
                    cnn.Close();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }
    }
}
